package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaRMParams;
import com.mbc.rmt.data.SavingDataList;
import com.mbc.rmt.entities.Saving;
import com.mbc.rmt.entities.SavingBalanceRpt;
import com.mbc.rmt.entities.SavingLedgerRpt;
import com.mbc.rmt.entities.SavingRpt;

public class SavingDAOImpls extends DAO<Saving> implements SavingDAO{

	public final String field_saving_id = "savingid";
	public final String field_saving_no = "savingno";
	public final String field_saving_date = "savingdate";
	public final String field_default_branch_id = "defaultbranchid";
	public final String field_created_by = "createdby";
	public final String field_modified_by = "modifiedby";
	public final String field_created_date = "createddate";
	public final String field_modified_date = "modifieddate";
	public final String field_agent_id = "agentid";
	public final String field_agent_code = "agentcode";
	public final String field_nrc = "nrc";
	public final String field_address = "address";
	public final String field_phone_no = "phoneno";
	public final String field_currency_id = "currencyid";
	public final String field_currencyrate_id = "currencyrateid";
	public final String field_currencyrate_code = "currencyratecode";
	public final String field_rate = "rate";
	public final String field_amount = "Amount";
	public final String field_bank_id = "bankid";
	public final String field_bank_code = "bankcode";
	public final String field_balance = "currentbalance";
	public final String field_transaction_type = "transactiontype";
	public final String field_agentname = "name";
	
	public SavingDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "Saving";
	}
	
	@Override
	public void create(Saving entity) throws SQLException {
		query = "INSERT INTO Saving (SavingID,SavingNo,SavingDate,DefaultBranchID,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate," +
				         "AgentID,AgentCode,NRC,ADDRESS,Phoneno,CurrencyID,Rate,Amount,BankID,BankCode,TransactionType)" +
				         "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		         pstmt = conn.prepareStatement(query);
		updateRecord(pstmt, entity);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Saving find(long key) throws SQLException {
		Saving saving = null;
		query = getSelectQuery() + " where savingid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			saving = readRecord(rs);
		rs.close();
		pstmt.close();
		return saving;
	}
	
	@Override
	public void update(Saving entity) throws SQLException {
		query = "update "+ table_name 
				+ " set savingdate = ?, modifiedby = ?,modifieddate = ?,agentId = ?,agentcode = ?,nrc = ?,address = ?,phoneno = ?, currencyId = ?, rate = ?, amount = ?, bankId = ?,bankcode= ?"
				+ " where savingid = " + entity.getSavingid();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new Timestamp(entity.getSavingdate().getTime()));
		pstmt.setLong(i++, entity.getModifieduser());
		/*pstmt.setDate(i++, new java.sql.Date(entity.getSavingdate()
				.getTime()));*/
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifieddate().getTime()));
		pstmt.setLong(i++, entity.getAgentid());
		pstmt.setString(i++, entity.getAgentcode());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setLong(i++, entity.getCurrencyid());
		pstmt.setDouble(i++, entity.getExchangerate());
		pstmt.setDouble(i++, entity.getAmount());
		if(entity.getBankid()!=null)
			pstmt.setLong(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(Saving saving) throws SQLException {
		query = "DELETE FROM "+table_name+" WHERE savingid = "+saving.getSavingid()+" AND transactiontype = "+saving.getTransactiontype();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	@Override
	public Saving getSaving(Saving entity) throws SQLException {
		Saving saving = null;
		query ="DECLARE @homecurrencyid bigint " +
			    " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
			    " select savingid,savingno,savingdate,s.agentid,s.agentcode,at.name,s.nrc,s.ADDRESS,phoneno," +
			    " s.currencyid,ISNULL(cr.currencyrateid,0) currencyrateid,(CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=s.currencyid" +
			    " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
			    " rate,amount,bankid,bankcode,transactiontype,a.currentbalance" +
			    " from Saving s" +
			    " INNER JOIN agentbalance a ON a.agent_id = s.agentid AND a.currencyid = s.currencyid" +
			    " INNER JOIN Currency c ON s.currencyid=c.currencyid" +
			    " Inner join agent at on at.agent_id = s.agentid" +
			    " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = s.currencyid" +
			    " AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+entity.getBranchId()+ 
				" where savingno = '"+entity.getSavingno()+"' And s.transactiontype = " + entity.getTransactiontype()+
				" and "+field_default_branch_id+ "=" + entity.getBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			saving = readRecords(rs);
		rs.close();
		pstmt.close();
		return saving;
	}

	
	@Override
	public SavingDataList findSaving(CriteriaRMParams cri) throws SQLException {
		SavingDataList l_DataList = new SavingDataList();
		ArrayList<Saving> l_entityList = new ArrayList<Saving>();
		String l_cri =" and "+field_default_branch_id+ "=" + cri.getBranchId() +"and " +
				field_transaction_type +"="+cri.getStatus();
		
		if(!cri.getFieldname().equals("SavingDate"))
			l_cri += "and s." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
		else
			l_cri += "and Convert(varchar(100), s." + cri.getFieldname() + ", 103) like N'%" + cri.getKeyword() + "%'";
		
		query = "DECLARE @homecurrencyid bigint " +
				    " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				    " set rowcount 10" +
				    " select * from (select row_number() over(order by savingno desc) as [rowno],savingid,savingno,savingdate,s.agentid,s.agentcode,at.name,s.nrc,s.ADDRESS,phoneno," +
				    " s.currencyid,ISNULL(cr.currencyrateid,0) currencyrateid,(CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=s.currencyid" +
				    " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
				    " rate,amount,bankid,bankcode,transactiontype,a.currentbalance" +
				    " from Saving s" +
				    " INNER JOIN agentbalance a ON a.agent_id = s.agentid AND a.currencyid = s.currencyid" +
				    " INNER JOIN Currency c ON s.currencyid=c.currencyid" +
				    " Inner join agent at on at.agent_id = s.agentid" +
				    " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = s.currencyid" +
				    " AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId()+ 
				    " where 1 = 1 " + l_cri + ") " + table_name+
				    " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = " and (SavingNo like N'%" + cri.getKeyword()
				+ "%')" + " and defaultbranchid = " + cri.getBranchId()
				+" and "+field_transaction_type +"="+cri.getStatus();
		query = "select count(savingid) from " + table_name + " where 1 = 1"
				+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	@Override
	protected Saving readRecord(ResultSet rs) throws SQLException {
		Saving entity = new Saving();
		entity.setSavingid(rs.getLong(field_saving_id));
		entity.setSavingno(rs.getString(field_saving_no));
		entity.setSavingdate(rs.getDate(field_saving_date));
		entity.setAgentid(rs.getLong(field_agent_id));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setNrc(rs.getString(field_nrc));
		entity.setAddress(rs.getString(field_address));
		entity.setPhone(rs.getString(field_phone_no));
		entity.setCurrencyid(rs.getLong(field_currency_id));
		entity.setExchangerate(rs.getDouble(field_rate));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setBankid(rs.getLong(field_bank_id));
		entity.setBankcode(rs.getString(field_bank_code));
		return entity;
	}

	protected Saving readRecords(ResultSet rs) throws SQLException {
		Saving entity = new Saving();
		entity.setSavingid(rs.getLong(field_saving_id));
		entity.setSavingno(rs.getString(field_saving_no));
		entity.setSavingdate(rs.getDate(field_saving_date));
		entity.setAgentid(rs.getLong(field_agent_id));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setAgentname(rs.getString(field_agentname));
		entity.setNrc(rs.getString(field_nrc));
		entity.setAddress(rs.getString(field_address));
		entity.setPhone(rs.getString(field_phone_no));
		entity.setCurrencyid(rs.getLong(field_currency_id));
		entity.setRateid(rs.getLong(field_currencyrate_id));
		entity.setRatecode(rs.getString(field_currencyrate_code));
		entity.setExchangerate(rs.getDouble(field_rate));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setBankid(rs.getLong(field_bank_id));
		entity.setBankcode(rs.getString(field_bank_code));
		entity.setTransactiontype(rs.getByte(field_transaction_type));
		entity.setBalance(rs.getDouble(field_balance));
		return entity;
	}
	
	protected void updateRecord(PreparedStatement pstmt, Saving entity)
			throws SQLException {
		int i = 1;
		
		pstmt.setLong(i++, entity.getSavingid());
		pstmt.setString(i++, entity.getSavingno());
		pstmt.setTimestamp(i++, new Timestamp(entity.getSavingdate().getTime()));
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getCreateduser());
		pstmt.setLong(i++, entity.getModifieduser());
		pstmt.setTimestamp(i++, new Timestamp(entity.getCreateddate().getTime()));
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifieddate().getTime()));
		pstmt.setLong(i++, entity.getAgentid());
		pstmt.setString(i++, entity.getAgentcode());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setLong(i++, entity.getCurrencyid());
		pstmt.setDouble(i++, entity.getExchangerate());
		pstmt.setDouble(i++, entity.getAmount());
		if(entity.getBankid()!=null)
			pstmt.setLong(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.setLong(i++, entity.getTransactiontype());
	}

	@Override
	public ResultSet prepareSavingReport(SavingRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_SavingRpt ?, ?, ?, ?, ?, ?, ? , ? , ? , ?, ?, ? , ? , ?,  ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		SavingRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}	
	
	private static void SavingRptParameter(CallableStatement l_pstmt,
			SavingRpt  info) throws SQLException {
		int i = 1;
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromdate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getTodate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromsaving());
		l_pstmt.setString(i++, info.getTosaving());
		l_pstmt.setString(i++, info.getFromagentid());
		l_pstmt.setString(i++, info.getToagentid());
		l_pstmt.setString(i++, info.getFromtypeid());
		l_pstmt.setString(i++, info.getTotypeid());
		l_pstmt.setString(i++, info.getFromcurrencyid());
		l_pstmt.setString(i++, info.getTocurrencyid());
		l_pstmt.setString(i++, info.getFrombankid());
		l_pstmt.setString(i++, info.getTobankid());
		l_pstmt.setString(i++, info.getFromuserid());
		l_pstmt.setString(i++, info.getTouserid());
		l_pstmt.setInt(i++, info.getTranstatus());
		l_pstmt.setLong(i++, info.getBranchid());
		l_pstmt.setString(i++, info.getOrderby());
	}

	@Override
	public ResultSet prepareSavingBalanceReport(SavingBalanceRpt info)
			throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_SavingBalanceRpt ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		SavingBalanceRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void SavingBalanceRptParameter(CallableStatement l_pstmt,
			SavingBalanceRpt  info) throws SQLException {
		int i = 1;		
		l_pstmt.setString(i++, info.getFromAgentID());
		l_pstmt.setString(i++, info.getToAgentID());		
		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getGroupby());
		l_pstmt.setLong(i++, info.getBranchid());
	}
	
	@Override
	public ResultSet prepareSavingLedgerReport(SavingLedgerRpt info)
			throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_SavingLedger_Rpt ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		SavingLedgerRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private static void SavingLedgerRptParameter(CallableStatement l_pstmt,
			SavingLedgerRpt  info) throws SQLException {
		int i = 1;
		l_pstmt.setString(i++, info.getFromdate());
		l_pstmt.setString(i++, info.getTodate());		
		l_pstmt.setString(i++, info.getFromAgentID());
		l_pstmt.setString(i++, info.getToAgentID());		
		l_pstmt.setString(i++, info.getCurrencyID());
		l_pstmt.setBoolean(i++, info.getChkSpecified());
		l_pstmt.setLong(i++, info.getBranchid());
	}
}
